﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Backend
{
    /* Employee Class
     * This class will handle all the employees and the methods 
     * to create, search, remove and update their information */
    class Employee
    {
		DBConnect dbconn = new DBConnect();

        public DataTable List()
        {
            DataTable customerTable = dbconn.ReturnDataTable("employee");
            return customerTable;
        }

        public DataTable Specialities(int eid)
        {
            DataTable specialityTable = dbconn.BetterDataTable("SELECT b.bid, b.name FROM specialization AS s, brand AS b WHERE s.eid = '" + eid + "' AND s.bid = b.bid", 2);
            return specialityTable;
        }

        public string[] GetInfo(int eid)
        {
            string[] employeeInfo = dbconn.ReturnArray("SELECT eid, fullname, initials FROM employee WHERE eid = '" + eid + "'", 3);
            return employeeInfo;
        }

        public bool CheckEmployee(int eid)
        {
			int results;
			string query;

			query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM employee WHERE eid = '" + eid + "'");
			results = Convert.ToInt32(query);

            if (results > 0)
                return true;
            else
                return false;
        }

		public void Create(string fullName, string initials, out bool result, out int eid)
		{
			int countRow;
			string query;

			query = dbconn.RunQueryReturn("SELECT COUNT(*) FROM employee WHERE initials = '" + initials + "'");
			countRow = Convert.ToInt32(query);

            if (countRow > 0)
            {
                result = false;
                eid = 0;
            }
            else
            {
                string[] insertSet = new string[2];

                insertSet[0] = fullName; // Employee's fullname
                insertSet[1] = initials; // Employee's initials

                dbconn.RunQuery("INSERT INTO employee (fullname, initials) VALUES ('" + string.Join("', '", insertSet) + "')");
                eid = Convert.ToInt32(dbconn.RunQueryReturn("SELECT MAX(eid) FROM employee"));
                result = true;
            }
		}

		public bool Update(int eid, string fullName, string initials)
		{
			if (CheckEmployee(eid) != true)
				return false;
			else
			{
                string[] updateSet = new string[2];

                updateSet[0] = "fullname = '" + fullName + "'"; // Employee's fullname
                updateSet[1] = "initials = '" + initials + "'"; // Employee's initials

                dbconn.RunQuery("UPDATE employee SET " + string.Join(", ", updateSet) + " WHERE eid = '" + eid + "' LIMIT 1");
				return true;
			}
		}

		public bool Remove(int eid)
		{
            if (CheckEmployee(eid) != true)
				return false;
			else
			{
				dbconn.RunQuery("DELETE FROM employee WHERE eid = '" + eid + "'");
				return true;
			}
		}

        public bool AddSpeciality(int eid, int bid)
        {
            if (CheckEmployee(eid) != true)
                return false;
            else
            {
                Brand brand = new Brand();

                if (brand.CheckBrand(bid) != true)
                    return false;
                else
                {
                    dbconn.RunQuery("INSERT INTO specialization VALUES ('" + eid + "', '" + bid + "')");
                    return true;
                }
            }
        }

        public bool RemoveSpeciality(int eid, int bid)
        {
            if (CheckEmployee(eid) != true)
                return false;
            else
            {
                Brand brand = new Brand();

                if (brand.CheckBrand(bid) != true)
                    return false;
                else
                {
                    dbconn.RunQuery("DELETE FROM specialization WHERE eid = '" + eid + "' AND bid = '" + bid + "'");
                    return true;
                }
            }
        }
    }
}
